OpenEdge Development:<br />Progress 4GL Handbook Using indexes properly

A detailed discussion of index design is beyond the scope of this book. But this section offers a few guidelines that relate directly to how proper use of indexes can contribute to the performance of your application.

Using word indexes for status indicators

A word index is a special index type that you can define for a character field. It indexes not the entire field value, as a normal index would, but every individual word in the index. There are delimiters you can define to tell the database manager just what you would like to see treated as a word, what the delimiters between words are, and so forth. You can learn about word indexes in OpenEdge Development: Programming Interfaces . Word indexes can be a tremendously powerful mechanism for identifying database fields that contain particular words. In fact, there is a special CONTAINS operator for a WHERE clause, similar to BEGINS and MATCHES, which is reserved for use with word indexes.

One powerful use of word indexes is not just to provide an index on all the words in a free text field, such as a status message or customer comments, but to create special character fields in which you store strings that identify other aspects of the record. For example, you can create a character field for a table in which you store various attributes of the record that otherwise would be individual logical fields with true/false values. It’s much more efficient to use the CONTAINS operator on a word-indexed field than to evaluate a number of different indexed fields. You can also store some combination of field names and field values in a word-indexed field to make it easier and faster to find a record based on a number of different search criteria, such as customers where you have some particular bits and pieces of name and address information.

Avoiding indexes on logical values

If your application needs to identify records that satisfy some Boolean condition (such as Active vs. Inactive, Male vs. Female, or Domestic vs. Foreign), it is not a good idea to do this by means of indexes on Logical fields that represent the two conditions. The same is true of other fields that have only a handful of values, whether they are character values, such as Foreign and Domestic, or integer values representing those meanings. An index bracket is the portion of the index that the OpenEdge RDBMS must search through to identify all the records that match your selection criteria. If this is half or a large fraction of all the records, then the index is not serving its purpose and data retrieval is not efficient. Instead, you should consider encoding these kinds of values in a word-indexed character field. Under very special circumstances it might be beneficial to define an index on a logical value when, for example, 99 percent of the records are true for that value and you frequently need to identify the one percent that are false.

Using multi-component indexes

You can define an index on one or more fields in a table. Defining a multi-component index can be much more effective than defining multiple indexes on the same individual fields, but only when your application needs to access that combination of fields in the order in which they appear in the index. For example, if your application sometimes needs to select data based on the value of field A, and sometimes on A and B together, and sometimes on A, B, and C, then it makes sense to define a multi-component index with fields A, B, and C in that order.

However, if your application sometimes needs to select data based just on field B, or on C, or on B and C together, without knowing the value of A, then this index will do you no good any more than you can easily locate a word in the dictionary by knowing the second or third letter in the word.

Always evaluate the selection requirements of your application carefully as you design your database indexes.

Avoiding unneeded indexes

Another danger is simply defining too many indexes on a table. You should define an index for a table when you have most or all of these requirements:

The index greatly reduces the amount of data to search to locate needed records. You should avoid indexes on small numbers of distinct values.

The index is needed frequently. If only one occasionally used procedure needs some unusual selection, it is probably not worth defining an index just for that case.

Fast performance is essential for the procedure that uses the index. If you have a batch report that runs once a month that needs some special selection criteria, it probably isn’t worth defining an index just for that purpose.

Maintaining an index every time you create or update a record is relatively expensive. Maintaining many indexes on the same table can be very expensive. Avoid defining indexes you don’t really need.